﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using WeifenLuo.WinFormsUI.Docking;
using Infragistics.Win;
using Infragistics.Win.UltraWinGrid;
using Infragistics.Win.UltraWinCalcManager;
using Infragistics.Win.CalcEngine;
using System.Collections;
using Infragistics.Excel;
using MoneyConvertChinese;
using System.IO;
using GoldPrinter;

namespace BYManageInfoSystem
{
    public partial class Frm_FeeFacCheckSheet : DockContent
    {
        private bool m_bHaveSetFontSize = false;
        private bool m_bSheet = false;
        ArrayList m_lstExcelFileName = new ArrayList();
        private Decimal m_dtTemp = 0;
        private string m_strComp = "";
        private string m_strFac = "";
        private string m_strFullFac = "";
        private string m_strPerson = "";
        private string m_strDept = "";
        private DGBY.BLL.HxdNoTab m_bllHxdNoTab = new DGBY.BLL.HxdNoTab();
        private string m_strFolder = "d:\\费用工厂对账单\\";
        private DGBY.BLL.T_ACL_User m_bllT_ACL_User = new DGBY.BLL.T_ACL_User();
        private DGBY.BLL.BYCompBankCountTab m_bllBYCompBankCountTab = new DGBY.BLL.BYCompBankCountTab();
        private DGBY.BLL.FactoryDataTab m_bllFactoryDataTab = new DGBY.BLL.FactoryDataTab();
        private DGBY.BLL.FacFeeTabNew m_bllFacFeeTabNew = new DGBY.BLL.FacFeeTabNew();
        private DGBY.BLL.BYDepartTab m_bllBYDepartTab = new DGBY.BLL.BYDepartTab();
        private bool m_bInsertFlag = false;
        private int m_nDelIndex = -1;

        public Frm_FeeFacCheckSheet()
        {
            InitializeComponent();
        }

        private void InitCombox()
        {
            DataSet ds = m_bllFactoryDataTab.GetList("");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                this.comboBoxFac.DataSource = ds.Tables[0];

                this.comboBoxFac.ValueMember = "FactoryDataTabID";
                this.comboBoxFac.DisplayMember = "工厂简称";
                this.comboBoxFac.AutoCompleteSource = AutoCompleteSource.ListItems;
                this.comboBoxFac.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
         
                this.comboBoxFac.SelectedIndex = 0;
            }
            ds = m_bllT_ACL_User.GetList("Name != 'System1'");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                this.comboBoxPerson.DataSource = ds.Tables[0];
                this.comboBoxPerson.DisplayMember = "用户名称";
                this.comboBoxPerson.ValueMember = "UserPasswordTabID";
                this.comboBoxPerson.AutoCompleteSource = AutoCompleteSource.ListItems;
                this.comboBoxPerson.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
                this.comboBoxPerson.SelectedIndex = 0;
            }
            ds = m_bllBYDepartTab.GetList("");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                this.comboBoxDept.DataSource = ds.Tables[0];
                this.comboBoxDept.DisplayMember = "部门名称";
                this.comboBoxDept.ValueMember = "BYDepartTabID";
                this.comboBoxDept.SelectedIndex = 0;
            }
        }

        private void Frm_FeeFacCheckSheet_Load(object sender, EventArgs e)
        {
            InitCombox();
            ClassUltraGridCommonSet.InitUltraWinGridSetting(this.ultraGrid1);
        }

        private void ShowFacCheckSheet(string strWhere, int nYearMonth, string strFacName)
        {
      
            
            DataSet m_ds = m_bllFacFeeTabNew.GetFacCheckSheet(strWhere, nYearMonth, strFacName);
            ultraGrid1.DataSource = null;

            if (m_ds.Tables.Count > 0 && m_ds.Tables[0].Rows.Count > 0)
            {
                //bool bIsSpecialFac = (bool)m_ds.Tables[0].Rows[0]["是否特殊工厂"];
                int nGuiNum = 0;
                Decimal dtFeeTotal = 0;
                Decimal dtSjFee = 0;
                Decimal dtBGTGDFee = 0;

                Decimal dtBGBGFee = 0;
                Decimal dtBGGKJSFee = 0;
                Decimal dtBGLGFee = 0;
                Decimal dtBGJYFee = 0;
                Decimal dtBGKAFee = 0;
                Decimal dtBGCGFee = 0;
                Decimal dtBGGCFee = 0;
                Decimal dtBGSMFee = 0;
                Decimal dtBGCSFee = 0;
                Decimal dtBGFHCGFee = 0;
                Decimal dtBGRCDDFee = 0;
                Decimal dtBGOtherFee = 0;
                Decimal dtDlcwFee = 0;
                Decimal dtHkcwFee = 0;
                Decimal dtDhlFee = 0;
                Decimal dtDzFee = 0;
                Decimal dtJcfFee = 0;
                Decimal dtZxzFee = 0;
                Decimal dtSpecialFee = 0;

                bool bHasDingDanCode = false;
                for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
                {
                    nGuiNum += Convert.ToInt32(m_ds.Tables[0].Rows[i]["柜数"].ToString(), 10);
                    dtFeeTotal += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["费用合计"].ToString());
                    dtSjFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["商检费"].ToString());
                    dtBGTGDFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["通关单费"].ToString());
                    dtBGBGFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["报关费"].ToString());
                    dtBGGKJSFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["港口建设费"].ToString());
                    dtBGLGFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["连柜费"].ToString());
                    dtBGJYFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["加页费"].ToString());
                    dtBGKAFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["GPS费"].ToString());
                    dtBGCGFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["查柜费"].ToString());
                    dtBGGCFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["改船费"].ToString());
                    dtBGSMFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["扫描费"].ToString());
                    dtBGCSFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["茶水费"].ToString());
                    dtBGFHCGFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["放行后查柜费"].ToString());
                    dtBGRCDDFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["入仓代垫费"].ToString());
                    dtBGOtherFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["报关其他费用"].ToString());
                    dtDlcwFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["大陆船务费"].ToString());
                    dtHkcwFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["香港船务费"].ToString());
                    dtDhlFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["国外快递费"].ToString());
                    dtDzFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["单证费"].ToString());
                    dtJcfFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["检测费"].ToString());
                    dtZxzFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["纸箱证"].ToString());
                    dtSpecialFee += Convert.ToDecimal(m_ds.Tables[0].Rows[i]["特殊费"].ToString());

                    if (!bHasDingDanCode && !string.IsNullOrEmpty(m_ds.Tables[0].Rows[i]["订单号"].ToString()))
                        bHasDingDanCode = true;
                }
                ultraGrid1.DataSource = m_ds.Tables[0];
                DataRow dr = m_ds.Tables[0].NewRow();
                dr["柜数"] = nGuiNum;
                //decimal dtPrivateMaybe = 0;//除商检，报关，大陆船务之外费用合计

                dr["费用合计"] = dtFeeTotal;
                //if (dtFeeTotal > 0)
                //{
                //    dr["费用合计"] = dtFeeTotal;
                //}
                //else
                //{
                //    ultraGrid1.DisplayLayout.Bands[0].Columns["费用合计"].Hidden = true;
                //}
                if (dtSjFee > 0)
                {
                    dr["商检费"] = dtSjFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["商检费"].Hidden = true;
                }
                if (dtBGTGDFee > 0)
                {
                    dr["通关单费"] = dtBGTGDFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["通关单费"].Hidden = true;
                }
                if (dtBGBGFee > 0)
                {
                    dr["报关费"] = dtBGBGFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["报关费"].Hidden = true;
                }
                if (dtBGGKJSFee > 0)
                {
                    dr["港口建设费"] = dtBGGKJSFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["港口建设费"].Hidden = true;
                }
                if (dtBGLGFee > 0)
                {
                    dr["连柜费"] = dtBGLGFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["连柜费"].Hidden = true;
                }
                if (dtBGJYFee > 0)
                {
                    dr["加页费"] = dtBGJYFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["加页费"].Hidden = true;
                }
                if (dtBGKAFee > 0)
                {
                    dr["GPS费"] = dtBGKAFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["GPS费"].Hidden = true;
                }
                if (dtBGCGFee > 0)
                {
                    dr["查柜费"] = dtBGCGFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["查柜费"].Hidden = true;
                }
                if (dtBGGCFee > 0)
                {
                    dr["改船费"] = dtBGGCFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["改船费"].Hidden = true;
                }
                if (dtBGSMFee > 0)
                {
                    dr["扫描费"] = dtBGSMFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["扫描费"].Hidden = true;
                }
                if (dtBGCSFee > 0)
                {
                    dr["茶水费"] = dtBGCSFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["茶水费"].Hidden = true;
                }
                if (dtBGFHCGFee > 0)
                {
                    dr["放行后查柜费"] = dtBGFHCGFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["放行后查柜费"].Hidden = true;
                }
                if (dtBGRCDDFee > 0)
                {
                    dr["入仓代垫费"] = dtBGRCDDFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["入仓代垫费"].Hidden = true;
                }
                if (dtBGOtherFee > 0)
                {
                    dr["报关其他费用"] = dtBGOtherFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["报关其他费用"].Hidden = true;
                }
                if (dtDlcwFee > 0)
                {
                    dr["大陆船务费"] = dtDlcwFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["大陆船务费"].Hidden = true;
                }
                if (dtHkcwFee > 0)
                {
                    dr["香港船务费"] = dtHkcwFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["香港船务费"].Hidden = true;
                }
                if (dtDhlFee > 0)
                {
                    dr["国外快递费"] = dtDhlFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["国外快递费"].Hidden = true;
                }
                if (dtDzFee > 0)
                {
                    dr["单证费"] = dtDzFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["单证费"].Hidden = true;
                }
                if (dtJcfFee > 0)
                {
                    dr["检测费"] = dtJcfFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["检测费"].Hidden = true;
                }
                if (dtZxzFee > 0)
                {
                    dr["纸箱证"] = dtZxzFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["纸箱证"].Hidden = true;
                }

                if (dtSpecialFee > 0)
                {
                    dr["特殊费"] = dtSpecialFee;
                }
                else
                {
                    ultraGrid1.DisplayLayout.Bands[0].Columns["特殊费"].Hidden = true;
                }

                dr["合同编号"] = "合计";
                m_ds.Tables[0].Rows.Add(dr.ItemArray);

                //if (!bIsSpecialFac)
                //{

                m_dtTemp = dtFeeTotal;
    
                  
                //this.textBoxPubRecvMoney.Text = "0";
                //}
                //else
                //{
                //    this.textBoxPrivateRecvMoney.Text = (dtHkcwFee + dtDhlFee + dtDzFee + dtJcfFee + dtZxzFee).ToString();
                //    this.textBoxPubRecvMoney.Text = (dtFeeTotal - (dtHkcwFee + dtDhlFee + dtDzFee + dtJcfFee + dtZxzFee)).ToString();
                //}

                //MoneyConvertChineseEx mcc = new MoneyConvertChineseEx();
                //string m_strJinEUpper = mcc.MoneyToChinese(dtFeeTotal.ToString(), true);

                ultraGrid1.DataSource = m_ds.Tables[0];
                this.ultraGrid1.DisplayLayout.Bands[0].Columns["柜号"].CellMultiLine = Infragistics.Win.DefaultableBoolean.True;
                ultraGrid1.DisplayLayout.Bands[0].Columns[0].Hidden = true;
                ultraGrid1.DisplayLayout.Bands[0].Columns[1].Hidden = true;
                ultraGrid1.DisplayLayout.Bands[0].Columns[2].Hidden = true;

                //如果订单号为空 隐藏
                if (!bHasDingDanCode)
                    ultraGrid1.DisplayLayout.Bands[0].Columns["订单号"].Hidden = true;
            }
  
        }
        private void ShowAllRecord(string strWhere)
        {
            ultraGrid1.Visible = true;
            int nYear = this.dateTimePicker1.Value.Date.Year;
            int nMonth = this.dateTimePicker1.Value.Date.Month;
            int nYearMonth = 0;
            nYearMonth = nYear * 100 + nMonth;

            m_strFac = this.comboBoxFac.Text;

            ShowFacCheckSheet(strWhere, nYearMonth, m_strFac);
            this.textBoxPrivateRecvMoney.Text = m_dtTemp.ToString();

            DataSet ds = m_bllFactoryDataTab.GetList("FactoryDataTabID = '" +this.comboBoxFac.SelectedValue +  "'");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                m_strFullFac = ds.Tables[0].Rows[0]["工厂名称"].ToString();
                m_strComp = ds.Tables[0].Rows[0]["公司名称"].ToString();
            }
        }

        private void buttonShowAll_Click(object sender, EventArgs e)
        {
            ShowAllRecord("");
        }

        public static void ultraGrid1_InitializeRowFilter(Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs e)
        {
            //ultraGrid1.DisplayLayout.Override.RowSizing = RowSizing.AutoFree;
            //foreach (UltraGridColumn ugc in ultraGrid1.DisplayLayout.Bands[0].Columns)
            //    ugc.PerformAutoResize();
            // FILTER ROW FUNCTIONALITY RELATED ULTRAGRID SETTINGS
            //e.Layout.Override.RowFilterAction = RowFilterAction.DisableFilteredOutRows;
            // ----------------------------------------------------------------------------------
            // Enable the the filter row user interface by setting the FilterUIType to FilterRow.
            e.Layout.Override.FilterUIType = FilterUIType.FilterRow;
            //e.Layout.Override.RowFilterMode = RowFilterMode.AllRowsInBand;
            // FilterEvaluationTrigger specifies when UltraGrid applies the filter criteria typed 
            // into a filter row. Default is OnCellValueChange which will cause the UltraGrid to
            // re-filter the data as soon as the user modifies the value of a filter cell.
            e.Layout.Override.FilterEvaluationTrigger = FilterEvaluationTrigger.OnCellValueChange;
            //e.Layout.Override.RowFilterMode = RowFilterMode.AllRowsInBand;
            // By default the UltraGrid selects the type of the filter operand editor based on
            // the column's DataType. For DateTime and boolean columns it uses the column's editors.
            // For other column types it uses the Combo. You can explicitly specify the operand
            // editor style by setting the FilterOperandStyle on the override or the individual
            // columns.

            e.Layout.Override.FilterOperandStyle = FilterOperandStyle.DropDownList;

            // By default UltraGrid displays user interface for selecting the filter operator. 
            // You can set the FilterOperatorLocation to hide this user interface. This
            // property is available on column as well so it can be controlled on a per column
            // basis. Default is WithOperand. This property is exposed off the column as well.
            e.Layout.Override.FilterOperatorLocation = FilterOperatorLocation.WithOperand;

            // By default the UltraGrid uses StartsWith as the filter operator. You use
            // the FilterOperatorDefaultValue property to specify a different filter operator
            // to use. This is the default or the initial filter operator value of the cells
            // in filter row. If filter operator user interface is enabled (FilterOperatorLocation
            // is not set to None) then that ui will be initialized to the value of this
            // property. The user can then change the operator as he/she chooses via the operator
            // drop down.
            e.Layout.Override.FilterOperatorDefaultValue = FilterOperatorDefaultValue.StartsWith;

            // FilterOperatorDropDownItems property can be used to control the options provided
            // to the user for selecting the filter operator. By default UltraGrid bases 
            // what operator options to provide on the column's data type. This property is
            // avaibale on the column as well.
            //e.Layout.Override.FilterOperatorDropDownItems = FilterOperatorDropDownItems.All;

            // By default UltraGrid displays a clear button in each cell of the filter row
            // as well as in the row selector of the filter row. When the user clicks this
            // button the associated filter criteria is cleared. You can use the 
            // FilterClearButtonLocation property to control if and where the filter clear
            // buttons are displayed.
            e.Layout.Override.FilterClearButtonLocation = FilterClearButtonLocation.RowAndCell;

            // Appearance of the filter row can be controlled using the FilterRowAppearance proeprty.
            e.Layout.Override.FilterRowAppearance.BackColor = Color.LightYellow;

            // You can use the FilterRowPrompt to display a prompt in the filter row. By default
            // UltraGrid does not display any prompt in the filter row.
            e.Layout.Override.FilterRowPrompt = "Click here to filter data...";

            // You can use the FilterRowPromptAppearance to change the appearance of the prompt.
            // By default the prompt is transparent and uses the same fore color as the filter row.
            // You can make it non-transparent by setting the appearance' BackColorAlpha property 
            // or by setting the BackColor to a desired value.
            e.Layout.Override.FilterRowPromptAppearance.BackColorAlpha = Alpha.Opaque;

            // By default the prompt is spread across multiple cells if it's bigger than the
            // first cell. You can confine the prompt to a particular cell by setting the
            // SpecialRowPromptField property off the band to the key of a column.
            //e.Layout.Bands[0].SpecialRowPromptField = e.Layout.Bands[0].Columns[0].Key;

            // Display a separator between the filter row other rows. SpecialRowSeparator property 
            // can be used to display separators between various 'special' rows, including for the
            // filter row. This property is a flagged enum property so it can take multiple values.
            e.Layout.Override.SpecialRowSeparator = SpecialRowSeparator.FilterRow;

            // You can control the appearance of the separator using the SpecialRowSeparatorAppearance
            // property.
            e.Layout.Override.SpecialRowSeparatorAppearance.BackColor = Color.FromArgb(233, 242, 199);

            //   UltraGridBand band = e.Layout.Bands[0];
            //   band.ColumnFilters["原币金额"].FilterConditions.Clear();
            //   band.ColumnFilters["原币金额"].FilterConditions.Add(

            //FilterComparisionOperator.GreaterThan, 5);
            //   band.ColumnFilters["原币金额"].FilterConditions.Add(

            //FilterComparisionOperator.LessThan, 10);
            //   band.ColumnFilters["原币金额"].LogicalOperator = FilterLogicalOperator.And;
        }
        public void InitUltraWinGridSetting()
        {
            this.ultraGrid1.DisplayLayout.Override.RowSelectors = Infragistics.Win.DefaultableBoolean.True;
            this.ultraGrid1.DisplayLayout.Override.RowSelectorNumberStyle = Infragistics.Win.UltraWinGrid.RowSelectorNumberStyle.VisibleIndex;
            this.ultraGrid1.DisplayLayout.Override.RowSelectorWidth = 40;
        }
        private void ultraGrid1_InitializeLayout(object sender, InitializeLayoutEventArgs e)
        {
            if (this.checkBox1.Checked == true)
                ultraGrid1_InitializeRowFilter(e);
            else
                ClassUltraGridCommonSet.ultraGrid1_InitializeRowFilter(e);
            InitUltraWinGridSetting();
            InitColumnWidth();
            e.Layout.Override.RowSizing = RowSizing.AutoFree;
            e.Layout.Override.CellAppearance.TextVAlign = VAlign.Middle;
        }

        private void buttonExportToExcel_Click(object sender, EventArgs e)
        {
            DataTable dt = (DataTable)ultraGrid1.DataSource;

            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    SaveFileDialog sfd = new SaveFileDialog();
                    sfd.Filter = "Excel文件(*.xls)|*.xls";
                    string outFile = "";

                    if (sfd.ShowDialog() == DialogResult.OK)
                    {
                        outFile = sfd.FileName;
                    }
                    else
                    {
                        return;
                    }

                    ExportExcelFile(outFile);
                }
            }
        }

        private void ExportExcelFile(string outFile)
        {      
            ultraGridExcelExporter1.Export(ultraGrid1, outFile);

            //System.Threading.Thread.Sleep(200);
            //DoExtraJob(outFile);
        }

        private void ultraGridExcelExporter1_BeginExport(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.BeginExportEventArgs e)
        {

            //DataSet ds = m_bllFactoryDataTab.GetList("FactoryDataTabid = '"+ this.comboBoxFac.SelectedValue + "'");
            //string strFacName = ds.Tables[0].Rows[0]["国税登记的工厂名称"].ToString();
            //string strFacName = ds.Tables[0].Rows[0]["工厂名称"].ToString();
            string strFacName = m_strFullFac;
            Worksheet ws = e.CurrentWorksheet;

            ws.Rows[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
            ws.Rows[1].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
            ws.Rows[2].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
            ws.Rows[3].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
            ws.Rows[4].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;

            ws.Rows[0].Cells[4].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center;
            ws.Rows[1].Cells[4].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center;
            ws.Rows[0].Cells[4].CellFormat.Font.Height = 500;
            ws.Rows[1].Cells[4].CellFormat.Font.Height = 400;

            

            ws.Rows[0].Cells[4].Value = strFacName;
            ws.Rows[1].Cells[4].Value = this.dateTimePicker1.Value.Date.Year.ToString() + "年"
                + this.dateTimePicker1.Value.Date.Month.ToString().PadLeft(2, '0') + "月" 
                + @"费用明细表";

            ws.Rows[3].CellFormat.Font.Height = 300;

            ws.Rows[3].Cells[0].Value = @"发货人：" + strFacName;

            ws.Rows[3].Cells[5].Value = @"收货人：" + m_strComp;


            ws.Rows[4].CellFormat.Font.Height = 250;

            ws.Rows[4].Cells[0].Value = @"联系人：卢丽平 联系电话：0769-22421328(分机:832),QQ:2465095541,手机号码:13713336975,传真号码：0769-22429650";
            //ws.Rows[3].Cells[2].Value = @"传真号码：0769-22429650";
           
            //ws.Rows[4].CellFormat.Font.Height = 300;
            //ws.Rows[4].Cells[0].Value = @"联系电话：0769-22421328(分机:832),QQ:435071822,手机号码:13713336975";
            
            
            
            ////e.CurrentWorksheet.Workbook.ActiveWorksheet = ws;

            e.CurrentRowIndex = 6;
            ws.PrintOptions.Orientation = Infragistics.Excel.Orientation.Landscape;//打印横向
            ws.PrintOptions.BottomMargin = 0.2;
            ws.PrintOptions.TopMargin = 0.2;
            ws.PrintOptions.LeftMargin = 0.2;
            ws.PrintOptions.RightMargin = 0.2; 
    
            //ws.Workbook.DefaultFontHeight = 500;
        }

        private void ultraGridExcelExporter1_EndExport(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.EndExportEventArgs e)
        {
            DGBY.Model.BYCompBankCountTab modelBYCompBankCountTabPub = new DGBY.Model.BYCompBankCountTab();
            DGBY.Model.BYCompBankCountTab modelBYCompBankCountTabPri = new DGBY.Model.BYCompBankCountTab();
            DataSet ds = m_bllBYCompBankCountTab.GetAllAccountInfo();
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    int nID = Convert.ToInt32(ds.Tables[0].Rows[i]["BYCompBankCountTabID"].ToString(), 10);
                    string strComp = ds.Tables[0].Rows[i]["经营单位名称"].ToString();
                    if (strComp == "")
                        modelBYCompBankCountTabPri = m_bllBYCompBankCountTab.GetModel(nID);
                    if (strComp == "东莞市百业进出口有限公司")
                        modelBYCompBankCountTabPub = m_bllBYCompBankCountTab.GetModel(nID);
                }
            }

            Worksheet ws = e.CurrentWorksheet;
            int a = e.CurrentRowIndex;



            ws.Rows[a].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;

            ws.Rows[a].CellFormat.Font.Height = 300;
            ws.Rows[a].Cells[1].Value = "总费用";
           
            //Decimal dtTemp = Convert.ToDecimal(this.textBoxPrivateRecvMoney.Text) + Convert.ToDecimal(this.textBoxPubRecvMoney.Text);
            Decimal dtTemp = m_dtTemp;

            ws.Rows[a].Cells[2].Value = dtTemp.ToString("f2"); 
            MoneyConvertChineseEx mcc = new MoneyConvertChineseEx();
            string m_strJinEUpper = mcc.MoneyToChinese(dtTemp.ToString(), true);
            ws.Rows[a].Cells[3].Value = m_strJinEUpper;

            a++;

            ws.Rows[a + 1].CellFormat.Font.Height = 300;
            ws.Rows[a + 1].Cells[0].Value = "请贵司收到帐单核对无误后于10个工作日内汇款至如下帐户中:";

            ws.Rows[a + 1].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
            a++;
            if (m_dtTemp > 0)
            {
                a++;
                ws.Rows[a].CellFormat.Font.Height = 270;
                ws.Rows[a].Cells[0].Value = "1,";
                ws.Rows[a].Cells[1].Value = "汇款金额:";
                ws.Rows[a].Cells[2].Value = m_dtTemp.ToString("f2"); ;
                ws.Rows[a].Cells[3].Value = "开户名：" + modelBYCompBankCountTabPri.CountName
                    + " 开户行：" + modelBYCompBankCountTabPri.KHBank
                    + " 帐号：" + modelBYCompBankCountTabPri.CountNo;
                ws.Rows[a].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
            }
            a++;
            //if (Convert.ToDecimal(this.textBoxPubRecvMoney.Text) > 0)
            //{
            //    a++;
            //    ws.Rows[a].CellFormat.Font.Height = 300;
            //    if(Convert.ToDecimal(this.textBoxPrivateRecvMoney.Text) > 0)
            //        ws.Rows[a].Cells[0].Value = "2,";
            //    else
            //        ws.Rows[a].Cells[1].Value = "2,";
            //    ws.Rows[a].Cells[1].Value = "汇款金额:";
            //    ws.Rows[a].Cells[2].Value = Convert.ToDecimal(this.textBoxPubRecvMoney.Text).ToString("f2"); ;
            //    ws.Rows[a].Cells[3].Value = "开户名：" + modelBYCompBankCountTabPub.CountName
            //        + " 开户行：" + modelBYCompBankCountTabPub.KHBank
            //        + " 帐号：" + modelBYCompBankCountTabPub.CountNo;
            //}

            ws.Rows[++a].CellFormat.Font.Height = 300;
            ws.Rows[a].Cells[0].Value = "感谢贵司的支持,有您的支持与信任,我们会做得更好!!若有疑问,欢迎来电询问!";
            ws.Rows[a].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
            ws.Rows[++a].CellFormat.Font.Height = 300;
            ws.Rows[a].Cells[0].Value = "温馨提示:请贵公司于5个工作日内回传对帐差异,否则我司将视同默认,谢谢!";
            ws.Rows[a].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
            ws.Rows[++a].CellFormat.Font.Height = 300;
            ws.Rows[a].Cells[0].Value = "        请贵公司按上述指定时间汇款,否则我司将从贵公司货款中扣除费用款,谢谢!";
            ws.Rows[a].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
        }

        private void DoExtraJob(string outFile)
        {
            ExcelBase eB = new ExcelBase();

             try
             {
                 //eB.FacCheckSheet_DataTableToExcel(outFile, "页");
             }
             catch (System.Exception ex)
             {

             }

             eB.Close();
        }

        private void InitColumnWidth()
        {
            //int nWidth = 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["序号"].Width = 35;
            ultraGrid1.DisplayLayout.Bands[0].Columns["出货金额"].Width = 100;
            ultraGrid1.DisplayLayout.Bands[0].Columns["出货日期"].Width = 100;
            ultraGrid1.DisplayLayout.Bands[0].Columns["合同编号"].Width = 100;
            ultraGrid1.DisplayLayout.Bands[0].Columns["柜号"].Width = 130;
            ultraGrid1.DisplayLayout.Bands[0].Columns["柜数"].Width = 35;
            ultraGrid1.DisplayLayout.Bands[0].Columns["费用合计"].Width = 80;
            ultraGrid1.DisplayLayout.Bands[0].Columns["商检费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["通关单费"].Width = 60 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["报关费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["港口建设费"].Width = 70 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["连柜费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["加页费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["GPS费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["查柜费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["改船费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["扫描费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["茶水费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["报关其他费用"].Width = 80 + 40;
            ultraGrid1.DisplayLayout.Bands[0].Columns["大陆船务费"].Width = 70 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["香港船务费"].Width = 70 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["国外快递费"].Width = 70 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["单证费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["检测费"].Width = 40 + 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["纸箱证"].Width = 40 + 20;
        }
        private void button2_Click(object sender, EventArgs e)
        {
            m_bSheet = false;
            m_lstExcelFileName.Clear();
            ultraGrid1.Visible = false;
            m_strPerson = this.comboBoxPerson.Text;
            backgroundWorker1.RunWorkerAsync();
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
                        
            int nYear = this.dateTimePicker1.Value.Date.Year;
            int nMonth = this.dateTimePicker1.Value.Date.Month;
            int nYearMonth = 0;
            nYearMonth = nYear * 100 + nMonth;

            string strYear = this.dateTimePicker1.Value.Date.Year.ToString();
            string strMonth = this.dateTimePicker1.Value.Date.Month.ToString().PadLeft(2,'0');
            DataSet ds = m_bllHxdNoTab.GetFacSetsByPersonAndSBDate(m_strPerson, strYear + strMonth);

            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                string strNow = DateTime.Now.Date.ToShortDateString();
                //string strMonth = this.dateTimePickerTo.Value.Date.Month.ToString();
                if (!Directory.Exists(m_strFolder + strNow + @"\" + m_strPerson + @"\"))//判断是否存在
                {
                    Directory.CreateDirectory(m_strFolder + strNow + @"\" + m_strPerson + @"\");//创建新路径
                }


                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    //int nID = (int)ds.Tables[0].Rows[i]["FactoryDataTabID"];
                    m_strFac = ds.Tables[0].Rows[i]["工厂简称"].ToString();
                    m_strFullFac = ds.Tables[0].Rows[i]["工厂名称"].ToString();
                    m_strComp = ds.Tables[0].Rows[i]["经营单位"].ToString();
                    if (m_strFac == "")
                        continue;
                    ShowFacCheckSheet("", nYearMonth, m_strFac);
                    DataTable dt = (DataTable)ultraGrid1.DataSource;

                    if (dt != null)
                    {
                        if (dt.Rows.Count > 0)
                        {
                            string outFile = m_strFolder + strNow + @"\" + m_strPerson + @"\" + m_strFac + strMonth + "月对账单.xls";
                            if(m_bSheet)
                                m_lstExcelFileName.Add(outFile);

                            ExportExcelFile(outFile);
                        }
                    }

                    System.Threading.Thread.Sleep(2000);

                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            m_bSheet = true;
            m_lstExcelFileName.Clear();
            ultraGrid1.Visible = false;
            m_strPerson = this.comboBoxPerson.Text;
            backgroundWorker1.RunWorkerAsync();
       
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            string strNow = DateTime.Now.Date.ToShortDateString();
            if (m_bSheet && m_lstExcelFileName.Count >0)
            {
                ExcelBase eB = new ExcelBase();
                string outFile = m_strFolder + strNow + @"\" + m_strPerson + @"\" + m_strPerson + ".xls";
                eB.Books2SheetsEx(outFile, m_lstExcelFileName);
                eB.CloseAll();
            }
            MessageBox.Show("导出全部工厂对账单完成!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }

        private void ultraGridExcelExporter1_RowExported(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.RowExportedEventArgs e)
        {
            //Worksheet ws = e.CurrentWorksheet;
            //int a = e.CurrentRowIndex;
            //ws.Rows[a].CellFormat.Font.Height = 500;
        }

        private void buttonDepSheet_Click(object sender, EventArgs e)
        {
            
            m_bSheet = true;
            m_lstExcelFileName.Clear();
            ultraGrid1.Visible = false;
            m_strDept = this.comboBoxDept.Text;

            backgroundWorker2.RunWorkerAsync();
        }

        private void buttonDepFile_Click(object sender, EventArgs e)
        {
            m_bSheet = false;
            m_lstExcelFileName.Clear();
            ultraGrid1.Visible = false;
            m_strDept = this.comboBoxDept.Text;
            backgroundWorker2.RunWorkerAsync();
        }

        private void backgroundWorker2_DoWork(object sender, DoWorkEventArgs e)
        {
            
            int nYear = this.dateTimePicker1.Value.Date.Year;
            int nMonth = this.dateTimePicker1.Value.Date.Month;
            int nYearMonth = 0;
            nYearMonth = nYear * 100 + nMonth;

            string strYear = this.dateTimePicker1.Value.Date.Year.ToString();
            string strMonth = this.dateTimePicker1.Value.Date.Month.ToString().PadLeft(2,'0');
            DataSet ds = m_bllHxdNoTab.GetFacSetsByDept(m_strDept);

            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                string strNow = DateTime.Now.Date.ToShortDateString();
                //string strMonth = this.dateTimePickerTo.Value.Date.Month.ToString();
                if (!Directory.Exists(m_strFolder + strNow + @"\" + m_strDept + @"\"))//判断是否存在
                {
                    Directory.CreateDirectory(m_strFolder + strNow + @"\" + m_strDept + @"\");//创建新路径
                }


                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    //int nID = (int)ds.Tables[0].Rows[i]["FactoryDataTabID"];
                    m_strFac = ds.Tables[0].Rows[i]["工厂简称"].ToString();
                    m_strFullFac = ds.Tables[0].Rows[i]["工厂名称"].ToString();
                    m_strComp = ds.Tables[0].Rows[i]["经营单位"].ToString();
                    if (m_strFac == "")
                        continue;
                    ShowFacCheckSheet("", nYearMonth, m_strFac);
                    DataTable dt = (DataTable)ultraGrid1.DataSource;

                    if (dt != null)
                    {
                        if (dt.Rows.Count > 0)
                        {
                            string outFile = m_strFolder + strNow + @"\" + m_strDept + @"\" + m_strFac + strMonth + "月对账单.xls";
                            if(m_bSheet)
                                m_lstExcelFileName.Add(outFile);

                            ExportExcelFile(outFile);
                        }
                    }

                    System.Threading.Thread.Sleep(2000);

                }
            }
        }

        private void backgroundWorker2_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            string strNow = DateTime.Now.Date.ToShortDateString();
            if (m_bSheet && m_lstExcelFileName.Count > 0)
            {
                ExcelBase eB = new ExcelBase();
                string outFile = m_strFolder + strNow + @"\" + m_strDept + @"\" + m_strDept + ".xls";
                eB.Books2SheetsEx(outFile, m_lstExcelFileName);
                eB.CloseAll();
            }
            MessageBox.Show("导出全部工厂对账单完成!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void ultraGridExcelExporter1_InitializeRow(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.ExcelExportInitializeRowEventArgs e)
        {
            e.Row.Appearance.FontData.Bold = DefaultableBoolean.True;
            //e.Row.Appearance.FontData.SizeInPoints = 12;
        }

        private void ultraGridExcelExporter1_HeaderRowExported(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.HeaderRowExportedEventArgs e)
        {
            //e.GridRow.Appearance.FontData.Bold = DefaultableBoolean.True;
        }

        private void ultraGrid1_InitializeRow(object sender, InitializeRowEventArgs e)
        {
            if (!m_bHaveSetFontSize)
            {
                ultraGrid1.DisplayLayout.Override.HeaderAppearance.FontData.SizeInPoints = 12;
                ultraGrid1.DisplayLayout.Override.CellAppearance.FontData.SizeInPoints = 12;
                m_bHaveSetFontSize = true;
            }
        }

        private void ultraGridExcelExporter1_CellExported(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.CellExportedEventArgs e)
        {
            Worksheet ws = e.CurrentWorksheet;
            int iRdex = e.CurrentRowIndex;
            int iCdex = e.CurrentColumnIndex;
            
            if (e.GridColumn.Key == "出货金额")
            {
                string strMoneyValue = e.CurrentWorksheet.Rows[iRdex].Cells[iCdex].Value.ToString();
                if (!string.IsNullOrEmpty(strMoneyValue))
                {
                    Decimal dOutGoodsFee = Convert.ToDecimal(strMoneyValue);
                    string strMoney = FormatMoney(dOutGoodsFee);

                    e.CurrentWorksheet.Rows[iRdex].Cells[iCdex].Value = strMoney;
                }
            }
        }

        public string FormatMoney(Decimal dMoney)
        {
            string strMoney = dMoney.ToString("#,###0.00");
            return strMoney;
        }
    }
}
